package se2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.*;

public class PhoneAccount
{

	private Customer			customer;
	private int					id;
	private int					customerId;
	private double				balance;

	private int					extension;
	private String				ipAddress;
	private int					permissions;

	private int					billingPlan;
	private String				billingPlanName;
	private List<BillingPlan>	billingPlans;
	private List<CallRecord>	callRecord;
	private List<PhoneBill>		phoneBills;
	private String				msg = "";
	private String				accountStatus;
	private String				phoneStatus;

	public PhoneAccount()
	{

	}

	public PhoneAccount(int id)
	{
		this.id = id;
		getPhoneAccountFromDatabase();
	}

	public String getMsg()
	{
		return msg;
	}

	public void setMsg(String msg)
	{
		this.msg = msg;
	}

	public Customer getCustomer()
	{
		return customer;
	}

	public void setCustomer(Customer customer)
	{
		this.customer = customer;
	}

	public int getId()
	{
		return id;
	}

	public void setId(int id)
	{
		this.id = id;
	}

	public double getBalance()
	{
		return balance;
	}

	public String getAccountStatus()
	{
		return accountStatus;
	}

	public void setAccountStatus(String accountStatus)
	{
		this.accountStatus = accountStatus;
	}

	public String getPhoneStatus()
	{
		return phoneStatus;
	}

	public void setPhoneStatus(String phoneStatus)
	{
		this.phoneStatus = phoneStatus;
	}

	public String getFormattedBalance()
	{
		DecimalFormat dcf = new DecimalFormat("#0.00");
		dcf.setDecimalSeparatorAlwaysShown(true);
		return "$" + dcf.format(balance);
	}

	public void setBalance(double balance)
	{
		this.balance = balance;
	}

	public void updateBalance()
	{
		try
		{
			Connection conn = getConnection();
			PreparedStatement ps;

			ps = conn.prepareStatement("UPDATE phoneaccount SET balance = ? WHERE id = ?");
			ps.setDouble(1, this.balance);
			ps.setInt(2, this.id);
			ps.executeUpdate();

			conn.close();
			ps.close();
		}
		catch (Exception e)
		{
			System.out.println("Error updating balance");
			System.out.println(e);
		}
	}

	public int getExtension()
	{
		return extension;
	}

	public void setExtension(int extension)
	{
		this.extension = extension;
	}

	public String getIpAddress()
	{
		return ipAddress;
	}

	public void setIpAddress(String ipAddress)
	{
		this.ipAddress = ipAddress;
	}

	public int getPermissions()
	{
		return this.permissions;
	}

	public String getPermissionsNames()
	{
		switch (this.permissions)
		{
			case 0:
				return "Disabled";
			case 1:
				return "Incoming & Outgoing";
			case 2:
				return "Incoming Only";
			case 3:
				return "Outgoing Only";
			default:
				return "";
		}
	}

	public void setBillingPlanName(String billingPlanName)
	{
		this.billingPlanName = billingPlanName;
	}

	public String getBillingPlanName()
	{
		BillingPlan b = new BillingPlan();
		this.billingPlans = b.getAllBillingPlans();

		for (int i = 0; i < billingPlans.size(); i++)
		{
			if (billingPlans.get(i).getId() == this.billingPlan)
			{
				return billingPlans.get(i).getName();
			}
		}

		return "E:" + billingPlan;
	}

	public void setPermissions(int permissions)
	{
		this.permissions = permissions;
	}

	public int getBillingPlan()
	{
		return this.billingPlan;
	}

	public void setBillingPlan(int billingPlan)
	{
		this.billingPlan = billingPlan;
	}

	public List<CallRecord> getCallRecord()
	{
		return callRecord;
	}

	public void setCallRecord(List<CallRecord> callRecord)
	{
		this.callRecord = callRecord;
	}

	public List<PhoneBill> getPhoneBills()
	{
		return phoneBills;
	}

	public void setPhoneBills(List<PhoneBill> phoneBills)
	{
		this.phoneBills = phoneBills;
	}

	public int getCustomerId()
	{
		return customerId;
	}

	public void setCustomerId(int customerId)
	{
		this.customerId = customerId;
	}

	public String getCustomerName()
	{
		return customer.getName();
	}

	public String getCustomerEmail()
	{
		return customer.getEmail();
	}

	private Connection getConnection() throws ClassNotFoundException, SQLException
	{
		Class.forName("org.sqlite.JDBC");
		String home = System.getProperty("user.home");
		Connection conn = DriverManager.getConnection("jdbc:sqlite:" + home + "/db/VoIP");

		return conn;
	}

	public boolean addPhoneAccount(int id)
	{
		this.customerId = id;

		try
		{
			Connection conn = getConnection();
			PreparedStatement ps;
			ResultSet rs;

			ps = conn.prepareStatement("select * from phoneaccount where extension = ? and accountstatus <> 'cancelled'");
			ps.setInt(1, this.extension);
			rs = ps.executeQuery();

			if (rs.next())
			{
				rs.close();
				conn.close();
				this.msg = "Extension%20is%20already%20in%20use";
				return false;
			}
			rs.close();

			ps = conn.prepareStatement("select * from phoneaccount where ip = ? and accountstatus <> 'cancelled'");
			ps.setString(1, this.ipAddress);
			rs = ps.executeQuery();
			if (rs.next())
			{
				rs.close();
				conn.close();
				this.msg = "IP%20Address%20is%20already%20in%20use";
				return false;
			}
			rs.close();

			ps = conn
					.prepareStatement("insert into phoneaccount (ip, extension, cust_id, permissions, balance, accountStatus, phoneStatus) values ( ? , ? , ? , ? , 0, 'Activated', 'Stopped' )");
			ps.setString(1, this.ipAddress);
			ps.setInt(2, this.extension);
			ps.setInt(3, this.customerId);
			ps.setInt(4, this.permissions);
			ps.executeUpdate();

			ps = conn.prepareStatement("select id from phoneaccount where extension = ?");
			ps.setInt(1, this.extension);
			rs = ps.executeQuery();

			if (rs.next())
			{
				this.id = rs.getInt(1);
			}
			rs.close();

			ps = conn.prepareStatement("insert into billingtophone (plan_id, phone_id) values ( ? , ? )");
			ps.setInt(1, this.billingPlan);
			ps.setInt(2, this.id);
			ps.executeUpdate();

			Server.activatePhone(this.ipAddress);
			conn.close();

			return true;
		}
		catch (Exception e)
		{
			System.out.println("Exception HIT in PhoneAccount - addPhoneAccount()");
			e.printStackTrace();
			return false;
		}
	}

	public List<PhoneAccount> getAllPhoneAccount()
	{
		try
		{
			Connection conn = getConnection();
			PreparedStatement ps;

			List<PhoneAccount> phoneAccounts = new ArrayList<PhoneAccount>();

			ps = conn.prepareStatement("select id from PhoneAccount");

			ResultSet rs = ps.executeQuery();
			while (rs.next())
			{
				PhoneAccount pa = new PhoneAccount(rs.getInt(1));
				phoneAccounts.add(pa);
			}
			rs.close();
			conn.close();

			for (PhoneAccount pa : phoneAccounts)
			{
				Customer customer = new Customer(pa.getCustomerId());
				pa.setCustomer(customer);
			}

			return phoneAccounts;
		}
		catch (Exception e)
		{
			System.out.println("Query Error in BillingPlan.java - getAllBillingPlans()");
			System.out.println(e);
			return null;
		}
	}

	public List<PhoneAccount> getAllAssocPhoneAccounts()
	{
		try
		{
			Connection conn = getConnection();

			List<PhoneAccount> phoneAccounts = new ArrayList<PhoneAccount>();

			PreparedStatement ps = conn.prepareStatement("select id from PhoneAccount where cust_id = ? ");
			ps.setInt(1, this.customerId);
			ResultSet rs = ps.executeQuery();

			while (rs.next())
			{
				PhoneAccount pa = new PhoneAccount(rs.getInt(1));
				phoneAccounts.add(pa);
			}
			rs.close();

			conn.close();
			return phoneAccounts;
		}
		catch (Exception e)
		{
			System.out.println("Query Error in BillingPlan.java - getAllBillingPlans()");
			System.out.println(e);
		}

		return null;
	}

	public void getPhoneAccountFromDatabase()
	{
		try
		{
			Connection conn = getConnection();
			PreparedStatement ps;
			ResultSet rs;
			
			ps = conn.prepareStatement("select * from phoneaccount where id = ? ");
			ps.setInt(1, this.id);
			rs = ps.executeQuery();
			
			if (rs.next())
			{
				this.ipAddress = rs.getString(2);
				this.extension = rs.getInt(3);
				this.customerId = rs.getInt(4);
				this.balance = rs.getDouble(5);
				this.permissions = rs.getInt(6);
				this.accountStatus = rs.getString(7);
				this.phoneStatus = rs.getString(8);
			}
			rs.close();
			ps.close();

			ps = conn.prepareStatement("select plan_id from billingtophone where phone_id = ?");
			ps.setInt(1, this.id);
			rs = ps.executeQuery();
			if (rs.next())
			{
				this.billingPlan = rs.getInt(1);
			}
			rs.close();
			ps.close();

			conn.close();
		}
		catch (Exception e)
		{
			System.out.println("Exception HIT in PhoneAccount - getPhoneAccountFromDatabase()");
			e.printStackTrace();
		}
	}

	public boolean editPhoneAccount(int id, int ext, String ip, int perm, int bp)
	{

		try
		{
			Connection conn = getConnection();
			PreparedStatement ps;
			ResultSet rs;
			boolean needToUpdate = false;

			if (this.extension != ext)
			{
				ps = conn.prepareStatement("select * from phoneaccount where extension = ? and accountstatus <> 'cancelled' ");
				ps.setInt(1, this.extension);
				rs = ps.executeQuery();
				if (rs.next())
				{
					rs.close();
					ps.close();
					conn.close();
					this.msg = "Extension%20is%20already%20in%20use";
					return false;
				}
				rs.close();
				ps.close();
				needToUpdate = true;
			}

			if (!this.ipAddress.equals(ip))
			{
				ps = conn.prepareStatement("select * from phoneaccount where ip = ? and accountstatus <> 'cancelled'");
				ps.setString(1, this.ipAddress);
				rs = ps.executeQuery();
				if (rs.next())
				{
					rs.close();
					ps.close();
					conn.close();
					this.msg = "IP%20Address%20is%20already%20in%20use";
					return false;
				}
				rs.close();
				ps.close();
				needToUpdate = true;
			}
			
			Boolean b = true;

			if (needToUpdate || this.permissions != perm)
			{
				b = Server.updatePhone(ip, ext, perm, this.ipAddress, this.extension, this.permissions);
				if (b)
				{
					ps = conn.prepareStatement("update phoneaccount set ip = ? , extension = ? , permissions = ? where id = ? ");
					ps.setString(1, this.ipAddress);
					ps.setInt(2, this.extension);
					ps.setInt(3, this.permissions);
					ps.setInt(4, this.id);
					ps.executeUpdate();
	
					ps.close();
				}
			}

			if (b && this.billingPlan != bp)
			{
				ps = conn.prepareStatement("update billingtophone set plan_id = ? where phone_id = ? ");
				ps.setInt(1, this.billingPlan);
				ps.setInt(2, this.id);
				ps.executeUpdate();
				
				ps.close();
			}

			conn.close();

			return true;
		}
		catch (Exception e)
		{
			System.out.println("Exception HIT in PhoneAccount - addPhoneAccount()");
			this.msg = "Error%20with%20database";
			e.printStackTrace();
			return false;
		}
	}

	public boolean removePhoneAccount()
	{
		this.getPhoneAccountFromDatabase();
		
		try
		{
			Connection conn = getConnection();
			PreparedStatement ps;
			
			ps = conn.prepareStatement("update PhoneAccount set accountStatus='Cancelled', phoneStatus='Stopped' where id = ?");
			ps.setInt(1, this.id);
			ps.executeUpdate();

			ps.close();
			conn.close();

			return true;
		}
		catch (Exception e)
		{
			System.out.println("Exception HIT in PhoneAccount - addPhoneAccount()");
			this.msg = "Error%20with%20database%20when%20removing%20phone%20account";
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean suspendPhoneAccount()
	{
		try
		{
			Connection conn = getConnection();
			
			PreparedStatement ps;
			
			ps = conn.prepareStatement("update PhoneAccount set accountStatus='Suspended', phoneStatus='Stopped' where id = ?");
			ps.setInt(1, this.id);
			ps.execute();
			
			conn.close();
			
			Server.deactivatePhone(this.ipAddress);
			
			return true;
		}
		catch (Exception e)
		{
			System.out.println ("Fail suspend");
			System.out.println(e);
			return false;
		}
	}

	public boolean unsuspendPhoneAccount()
	{
		try
		{
			Connection conn = getConnection();
			
			PreparedStatement ps;
			
			ps = conn.prepareStatement("update PhoneAccount set accountStatus='Activated', phoneStatus='Running' where id = ?");
			ps.setInt(1, this.id);
			ps.execute();
			
			conn.close();
			
			Server.activatePhone(this.ipAddress);
			
			return true;
		}
		catch (Exception e)
		{
			System.out.println ("Fail unsuspend");
			System.out.println(e);
			return false;
		}
	}
	
}
